package com.example.room.dao;

import androidx.lifecycle.LiveData;
import androidx.room.Dao;
import androidx.room.Insert;
import androidx.room.Query;
import androidx.room.Transaction;

import com.example.room.UserWithOrders;
import com.example.room.entity.User;

import java.util.List;

@Dao
public interface UserDao {

    // 联表查询：获取指定用户及其所有订单信息
    @Transaction
    @Query("SELECT * FROM users WHERE user_id = :userId")
    LiveData<UserWithOrders> getUserWithOrders(long userId);

    // 插入用户
    @Insert
    long insert(User user);

    // 查询所有用户
    @Query("SELECT * FROM users")
    LiveData<List<User>> getAllUsers();

    @Query("SELECT * FROM users WHERE "
            + "user_name COLLATE NOCASE LIKE '%' || :name ||'%' OR "
            + "user_name_pinyin COLLATE NOCASE LIKE '%' || :namePinyin ||'%' OR "
//            + "user_name_pinyin COLLATE NOCASE LIKE '%' || :nameAbbreviation ||'%'"
            + "UPPER(user_name_pinyin) LIKE '%' || UPPER(:nameAbbreviation) || '%'"

    )
    LiveData<List<User>> searchUsers(String name, String namePinyin, String nameAbbreviation);

//    // ||相当于+号
//    @Transaction
//    @Query("SELECT * FROM users WHERE user_name LIKE '%' || :searchName || '%' ")
//    LiveData<List<User>> searchUsers(String searchName);

    @Query("SELECT * FROM users WHERE user_name COLLATE NOCASE LIKE '%' || :name || '%' || :namePinyin || '%'")
    LiveData<List<User>> searchUsers(String name, String namePinyin);


    //    @Query("SELECT * FROM users WHERE user_name_pinyin LIKE '%' || :namePinyin || '%' OR "
    @Query("SELECT * FROM users WHERE "
            + "user_name LIKE '%' || :name ||'%'"
            + " OR user_name LIKE '%' || :nameSplit ||'%'"// :何 || '%' || :洪 || '%' || :连
            + " OR user_name_pinyin COLLATE NOCASE LIKE '%' || :namePinyin || '%'"
            + " OR user_name_pinyin COLLATE NOCASE LIKE '%' || :namePinyinSplit || '%'"// :h || '%' || :h || '%' || :l
    )
    LiveData<List<User>> searchUsers(String name, String nameSplit, String namePinyin, String namePinyinSplit);

}

